const express = require('express');
const { handleError, executeQuery, formatDateTime } = require('../utils');
const router = express.Router();

// 查询罚款记录列表
router.post('/list', async (req, res) => {
    const { page = 1, pageSize = 10, search = '' } = req.body;
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    const searchQuery = `%${search}%`;

    const totalQuery = `
        SELECT COUNT(*) AS total
        FROM fines
        WHERE user_id LIKE ? OR borrow_id LIKE ?;
    `;

    const query = `
        SELECT f.*, br.journal_id, br.status AS borrow_status, u.name AS user_name
        FROM fines f
        JOIN borrowrecords br ON f.borrow_id = br.borrow_id
        JOIN users u ON f.user_id = u.user_id
        WHERE f.user_id LIKE ? OR f.borrow_id LIKE ?
        LIMIT ${limit} OFFSET ${offset};
    `;

    try {
        const totalResult = await executeQuery(totalQuery, [searchQuery, searchQuery]);
        const total = totalResult[0].total;
        const fines = await executeQuery(query, [searchQuery, searchQuery]);

        fines.forEach(fine => {
            fine.created_at = formatDateTime(fine.created_at);
            fine.updated_at = formatDateTime(fine.updated_at);
        });

        res.json({ total, page, pageSize, data: fines });
    } catch (error) {
        handleError(error, res);
    }
});

// 修改罚款记录（支付罚款并恢复借阅权限）
router.post('/update', async (req, res) => {
    const { fine_id } = req.body;

    // 查询罚款记录和相关借阅记录
    const fineQuery = `
        SELECT f.fine_id, f.borrow_id, f.user_id, f.fine_amount, f.status AS fine_status, br.status AS borrow_status
        FROM fines f
        JOIN borrowrecords br ON f.borrow_id = br.borrow_id
        WHERE f.fine_id = ?;
    `;

    const updateFineStatus = `
        UPDATE fines
        SET status = 'paid', updated_at = NOW()
        WHERE fine_id = ?;
    `;

    const updateBorrowRecordStatus = `
        UPDATE borrowrecords
        SET status = 'returned', return_date = NOW()
        WHERE borrow_id = ?;
    `;

    const restoreUserBorrowLimit = `
        UPDATE users
        SET borrow_limit = 1
        WHERE user_id = ?;
    `;

    try {
        const fineRecord = await executeQuery(fineQuery, [fine_id]);

        if (fineRecord.length === 0) {
            return res.status(404).json({ message: '罚款记录未找到' });
        }

        const { borrow_id, user_id, fine_status, borrow_status } = fineRecord[0];

        if (fine_status === 'paid') {
            return res.status(400).json({ message: '罚款已支付，无法修改' });
        }

        if (borrow_status === 'borrowed') {
            // 更新借阅记录为已归还
            await executeQuery(updateBorrowRecordStatus, [borrow_id]);

            // 恢复用户的借阅权限
            await executeQuery(restoreUserBorrowLimit, [user_id]);
        }

        // 更新罚款记录为已支付
        await executeQuery(updateFineStatus, [fine_id]);

        res.json({ message: '罚款记录修改成功，借阅记录已归还，用户借阅权限已恢复' });
    } catch (error) {
        handleError(error, res);
    }
});

// 删除罚款记录
router.post('/delete', async (req, res) => {
    const { fine_id } = req.body;

    const fineQuery = `
        SELECT f.fine_id, f.borrow_id, f.user_id, br.status AS borrow_status
        FROM fines f
        JOIN borrowrecords br ON f.borrow_id = br.borrow_id
        WHERE f.fine_id = ?;
    `;

    const deleteFineRecord = `
        DELETE FROM fines WHERE fine_id = ?;
    `;

    const updateUserBorrowLimit = `
        UPDATE users
        SET borrow_limit = ?
        WHERE user_id = ?;
    `;

    const selectStudentBorrowLimits = `
        select data_value
        from utils
        where utils_id=4;
    `;

    const selectTeacherBorrowLimits = `
    select data_value
    from utils
    where utils_id=5;
    `;

    const selectUserRole = `
        select role
        from users
        wher user_id=?;
    `;
    try {
        const fineRecord = await executeQuery(fineQuery, [fine_id]);

        if (fineRecord.length === 0) {
            return res.status(404).json({ message: '罚款记录未找到' });
        }

        const { borrow_id, user_id, borrow_status } = fineRecord[0];

        const UserRoleResult = await executeQuery(selectUserRole, [user_id]);
        UserRole = UserRoleResult[0]?.role;
        let NumOfBorrowLimits;
        if (UserRole === 'student')
        {
            const StudentBorrowLimitsRecord=await executeQuery(selectStudentBorrowLimits);
            NumOfBorrowLimits = StudentBorrowLimitsRecord[0]?.data_value;
        }    
        if (UserRole === 'teacher')
            {
                const TeacherBorrowLimitsRecord=await executeQuery(selectTeacherBorrowLimits);
                NumOfBorrowLimits = TeacherBorrowLimitsRecord[0]?.data_value;
            }    
        if (borrow_status === 'borrowed') {
            // 如果借阅记录还在借阅中，删除罚款时要恢复用户借阅权限
            await executeQuery(updateUserBorrowLimit, [NumOfBorrowLimits,user_id]);
        }

        // 删除罚款记录
        await executeQuery(deleteFineRecord, [fine_id]);

        res.json({ message: '罚款记录删除成功' });
    } catch (error) {
        handleError(error, res);
    }
});

module.exports = router;
